package at.socialconference.backend.persistence;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.Hashtable;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

import at.socialconference.backend.model.Conference;
import at.socialconference.backend.model.ConferenceParticipant;
import at.socialconference.backend.model.Country;
import at.socialconference.backend.model.Insertable;
import at.socialconference.backend.model.Login;
import at.socialconference.backend.model.Place;
import at.socialconference.backend.model.Relation;
import at.socialconference.backend.model.Talk;
import at.socialconference.backend.model.Topic;
import at.socialconference.backend.model.User;
import at.socialconference.inter.connection.protocol.SocketProtocol;

public class DBController {

	public static void insertObject(Insertable object) {
		Transaction tx = null;
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		try {
			tx = session.beginTransaction();
			session.save(object);
			tx.commit();
		} catch (RuntimeException e) {
			e.printStackTrace();
			if (tx != null && tx.isActive()) {
				try {
					tx.rollback();
				} catch (HibernateException e1) {
					System.out.println("ERROR");
				}
				throw e;
			}
		}
	}

	public static void deleteObject(Insertable object) {
		Transaction tx = null;
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		try {
			tx = session.beginTransaction();
			session.delete(object);
			tx.commit();
		} catch (RuntimeException e) {
			e.printStackTrace();
			if (tx != null && tx.isActive()) {
				try {
					tx.rollback();
				} catch (HibernateException e1) {
					System.out.println("ERROR");
				}
				throw e;
			}
		}
	}

	public static void updateObject(Insertable object) {
		Transaction tx = null;
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		try {
			tx = session.beginTransaction();

			if(object instanceof Relation){
				session.delete(object);
				session.save(object);
			}else{
				session.update(object);
			}
			tx.commit();
		} catch (RuntimeException e) {
			if (tx != null && tx.isActive()) {
				try {
					tx.rollback();
				} catch (HibernateException e1) {
					System.out.println("ERROR");
				}
				throw e;
			}
		}
	}

	/*
	 * public static List getPublicationsFromAuthor(String name){ Session
	 * session = HibernateUtil.getSessionFactory().getCurrentSession();
	 * session.beginTransaction(); Author author = (Author)
	 * session.load(Author.class, name); session.getTransaction().commit();
	 * return null; }
	 */

	@SuppressWarnings("unchecked")
	public static List<Country> getCountries() {

		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();

			List<Country> countries = (List<Country>) session.createQuery("select c from Country as c").list();

			System.out.println(countries.size());

			session.getTransaction().commit();
			return countries;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static Country getCountry(String cID) {
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();

			@SuppressWarnings("unchecked")
			List<Country> countries = (List<Country>) session.createQuery("select c from Country as c where id='" + cID + "'").list();

			session.getTransaction().commit();
			if (countries != null && countries.size() > 0)
				return countries.get(0);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static Login getLogin(String username) {
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			@SuppressWarnings("unchecked")
			List<Login> users = (List<Login>) session.createQuery("select c from Login as c where user='" + username + "'").list();

			session.getTransaction().commit();
			if (users != null && users.size() > 0)
				return users.get(0);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static User getUser(Integer uid) {
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			@SuppressWarnings("unchecked")
			List<User> users = (List<User>) session.createQuery("select c from User as c where userid=" + uid).list();

			session.getTransaction().commit();
			if (users != null && users.size() > 0)
				return users.get(0);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;

	}

	public static boolean existsLogin(String name) {
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();

			@SuppressWarnings("unchecked")
			List<Login> login = (List<Login>) session.createQuery("select c from Login as c where user='" + name + "'").list();

			session.getTransaction().commit();
			if (login != null && login.size() > 0)
				return true;
			return false;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return true;
	}

	@SuppressWarnings("unchecked")
	public static List<User> searchUser(String firstname, String lastname, Integer country) {

		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();

			String query = "select c from User as c";

			boolean existsFirstname = firstname != null && firstname.length() > 0;
			boolean existsLastname = lastname != null && lastname.length() > 0;

			if (existsFirstname || existsLastname)
				query += " where";

			if (existsFirstname) {
				query += " firstname like '%" + firstname + "%'";
				if (existsLastname) {
					query += " AND ";
				}
			}
			if (existsLastname) {
				query += " lastname like '%" + lastname + "%'";
			}
			List<User> users = (List<User>) session.createQuery(query).list();

			if (country != null && country.intValue() > 0) {
				List<User> users2 = new ArrayList<User>();

				for (User u : users) {
					if (u.getCountry() != null) {
						if (u.getCountry().getId().equals(country)) {
							users2.add(u);
						}
					}
				}

				users = users2;
			}

			session.getTransaction().commit();
			return users;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public static List<User> getUsers() {

		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();

			List<User> users = (List<User>) session.createQuery("select c from User as c").list();

			System.out.println(users.size());

			session.getTransaction().commit();
			return users;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}


	/**
	 * Retrieves a Conference from the Database
	 * @param id conference id
	 * @return Conference
	 */

	@SuppressWarnings("unchecked")
	public static Conference getConference(Integer id) {
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();

			List<Conference> conferences = (List<Conference>) session.createQuery("select c from Conference as c where conferenceid="+id).list();

			System.out.println(conferences.size());

			session.getTransaction().commit();
			if(conferences!=null && conferences.size()>0)
				return conferences.get(0);
			else
				return null;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public static List<Conference> getConferences() {

		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();

			List<Conference> conferences = (List<Conference>) session.createQuery("select c from Conference as c").list();


			session.getTransaction().commit();
			return conferences;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public static List<Talk> getTalks() {
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();

			List<Talk> talks = (List<Talk>) session.createQuery("select p from Talk as p").list();

			session.getTransaction().commit();
			return talks;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	
	@SuppressWarnings("unchecked")
	public static List<Place> getPlaces() {

		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();

			List<Place> places = (List<Place>) session.createQuery("select p from Place as p").list();

			session.getTransaction().commit();
			return places;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Check if User participates Conference
	 * @param userID user id
	 * @param conferenceID conference id
	 * @return true if user participates on conference
	 */
	public static boolean participatesConference(Integer userID, Integer conferenceID) {
		if(userID==null || conferenceID==null)
			return false;
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			String query = "select c from ConferenceParticipant as c where conferenceid="+conferenceID+" AND userid="+userID;

			@SuppressWarnings("unchecked")
			List<ConferenceParticipant> conferences = (List<ConferenceParticipant>) session.createQuery(query).list();
			session.getTransaction().commit();
			return conferences!=null && conferences.size()>0;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	@SuppressWarnings("unchecked")
	public static Conference getCurrentConference(Integer userid) {
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();

			List<Conference> cons = (List<Conference>) session.createQuery("select c from Conference as c").list();
			session.getTransaction().commit();
			if (cons != null) {
				for (Conference con : cons) {
					Date now = new Date();
					if (con.getDate_from().before(now) && con.getDate_to().after(now)) {
						if (participatesConference(userid,con.getId())) {						
							return con;
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Inserts new ConferenceParticipant into db
	 * @param userID 
	 * @param conferenceID
	 * @return true if success - conference is enrolled now for user
	 */
	public static boolean enroll(Integer userID, Integer conferenceID) {
		if(userID==null || conferenceID==null)
			return false;
		try {
			ConferenceParticipant cp = new ConferenceParticipant();
			cp.setUserid(userID);
			cp.setConferenceid(conferenceID);

			insertObject(cp);
			return true;
		}catch(Exception e){
			return false;
		}
	}



	@SuppressWarnings("unchecked")
	public static Talk getTalk(Integer id) {
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();

			List<Talk> talks = (List<Talk>) session.createQuery("select c from Talk as c where talkid=" + id).list();

			session.getTransaction().commit();
			return talks.get(0);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static void addUser(User u) {

		insertObject(u);
	}

	public static void addLogin(Login l) {
		insertObject(l);
	}

	public static void addFriend(int u1, int u2) {
		Relation rel = new Relation();
		rel.setIsAccepted(0);
		rel.setUserid_a(u1);
		rel.setUserid_b(u2);
		insertObject(rel);
	}

	/**
	 * 
	 * @param u1
	 *            First User
	 * @param u2
	 *            Second User
	 * @return 0 - NO FRIENDSHIP 1 - PENDING 2 - OK
	 */
	public static int checkFriendStatus(int u1, int u2) {
		Relation r = getRelation(u1, u2);
		if (r == null) {
			return 0; // NOFRIENDSHIP
		} else {
			if (r.getIsAccepted() != 1) {
				return 1; // PENDING
			} else {
				return 2; // OK
			}
		}
	}

	private static Relation getRelation(int u1, int u2) {
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			String query = "select c from Relation as c where (userid_a=" + u1 + " AND userid_b=" + u2 + ") OR ( userid_a=" + u2
			+ " AND userid_b=" + u1 + ")";
			@SuppressWarnings("unchecked")
			ArrayList<Relation> friends = (ArrayList<Relation>) session.createQuery(query).list();
			session.getTransaction().commit();
			if (friends != null && friends.size() > 0)
				return friends.get(0);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;

	}

	@SuppressWarnings("unchecked")
	private static HashSet<Integer> getRelationIDs(Integer id, boolean allFriends) {
		HashSet<Integer> friendIDs = new HashSet<Integer>();
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			String query = "select userid_a from Relation where userid_b=" + id;
			if (!allFriends) {
				query += " and accepted=1";
			}
			ArrayList<Integer> friendIDsA = (ArrayList<Integer>) session.createQuery(query).list();
			query = "select userid_b from Relation where userid_a=" + id;
			if (!allFriends) {
				query += " and accepted=1";
			}
			ArrayList<Integer> friendIDsB = (ArrayList<Integer>) session.createQuery(query).list();
			if (friendIDsA != null) {
				friendIDs.addAll(friendIDsA);
			}
			if (friendIDsB != null) {
				friendIDs.addAll(friendIDsB);
			}
			session.getTransaction().commit();
			return friendIDs;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;

	}

	public static Hashtable<Integer, User> getFriends(int id, boolean allFriends) {

		HashSet<Integer> intArr = getRelationIDs(id, allFriends);
		Hashtable<Integer, User> friends = new Hashtable<Integer, User>();
		if (intArr != null) {
			for (Integer uid : intArr) {
				User u = getUser(uid);
				if (u != null) {
					friends.put(uid, u);
				}
			}
		}
		return friends;
	}

	@SuppressWarnings("unchecked")
	public static List<User> getFriendRequests(Integer userid) {
		List<User> pendingFriends = new ArrayList<User>();
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			String query = "select userid_a from Relation where userid_b=" + userid + " and accepted=0";

			ArrayList<Integer> friendRequests = (ArrayList<Integer>) session.createQuery(query).list();
			session.getTransaction().commit();

			for (Integer uid : friendRequests) {
				User u = getUser(uid);
				if (u != null) {
					pendingFriends.add(u);
				}
			}

			return pendingFriends;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Accepts a friend request
	 */
	public static int acceptFriendRequest(int u1, int u2) {
		Relation r = getRelation(u1, u2);
		if (r == null) {
			return SocketProtocol.RESP_FSTATUS_NOFRIENDSHIP; // nothing to accept
		} else {
			if (r.getIsAccepted() != 1) {
				r.setIsAccepted(1);
				//deleteObject(r);
				updateObject(r);
				return SocketProtocol.OK; // OK
			} else {
				return SocketProtocol.RESP_FSTATUS_FRIEND; // friendship already exists
			}
		}
	}

	/**
	 * Rejects a friend request
	 */
	public static int rejectFriendRequest(int u1, int u2) {
		Relation r = getRelation(u1, u2);
		if (r == null) {
			return SocketProtocol.RESP_FSTATUS_NOFRIENDSHIP; // nothing to reject
		} else {
			deleteObject(r);
			return SocketProtocol.OK;
		}
	}

	public static int addTwitterTokens(Integer userid, String tw_acc, String tw_acc_secret) {
		User u = getUser(userid);
		if (u == null)
			return -1;
		u.setTw_access_token(tw_acc);
		u.setTw_access_secret(tw_acc_secret);

		updateObject(u);
		return 1;
	}

	@Test
	public void TestRelations() {
		// List<User> users = getUsers();
		//
		// User u1 = users.get(0);
		// User u2 = users.get(1);
		//
		// addFriend(u1, u2);
		//
		// users = getUsers();

		Hashtable<Integer, User> friends = getFriends(0, false);

		System.out.println(friends.get(new Integer(10)).getFirstname());
		System.out.println(friends.get(new Integer(11)).getFirstname());
		System.out.println(friends.get(new Integer(12)).getFirstname());
	}

	@Test
	public void test() {
		Login login = new Login();
		login.setUser("testihal4lo22e");
		login.setPwd("test22");

		User user = new User();
		user.setFirstname("Testi21222");
		user.setLastname("testtest332");
		user.setGender("m");
		Country c = getCountries().get(82);
		System.out.println(c.getId() + " " + c.getName());
		user.setCountry(c);

		Topic t1 = new Topic("1");
		user.addTopic(t1);
		t1 = new Topic("2");
		user.addTopic(t1);

		insertObject(user);

		login.setUserId(user.getId());

		insertObject(login);

	}

	@Test
	public void testGetLogin() {
		Login login = getLogin("testihal4lo22e");
		System.out.println(login.getPwd());
	}

	@Test
	public void testGetCurrentConference() {

		Conference c = getCurrentConference(20);
		List<Talk> t = c.getTalks();
		if(t!=null){
			System.out.println(t.size());
			if(t.size()>0){
				Talk talk = t.get(0);
				System.out.println(talk.getTitle());
				List<User> speakers = talk.getSpeakers();
				if(speakers!=null){
					System.out.println(speakers.size());
					if(speakers.size()>0){
						System.out.println(speakers.get(0).getLastname());
					}
				}
			}
		}
	}

	@Test
	public void testGetCountry() {
		Country c = getCountry("Austria");
		System.out.println(c.getName() + " " + c.getId());
	}

	@Test
	public void testExistsLogin() {
		boolean c = existsLogin("User");
		System.out.println(c);
		c = existsLogin("----");
		System.out.println(c);
	}

	@Test
	public void testSearchUser() {
		List<User> users = searchUser("Pat", null, null);
		System.out.println(users.size());
		users = searchUser("Patrick", "blabla", null);
		System.out.println(users.size());
		users = searchUser("Pat", null, new Integer(84));
		System.out.println(users.size());
	}

	@Test
	public void testFriendRequest() {
		List<User> ag = getFriendRequests(new Integer(3));
		System.out.println(ag);
	}

	@Test
	public void testFriendStatus() {
		System.out.println(checkFriendStatus(3, 1));
		System.out.println(checkFriendStatus(9, 3));
		System.out.println(checkFriendStatus(3, 9));
		System.out.println(checkFriendStatus(3, 11));
	}

	@Test
	public void testTwitterTokens() {
		addTwitterTokens(9, "test", "xxxxx");
	}

	@Test
	public void getConference() {
		Conference c = getConference(221689081);
		System.out.println(c.getName());
	}

	@Test
	public void testParticipatesConference() {
		System.out.println(participatesConference(20, 211959055));
		System.out.println(participatesConference(21, 211959055));
	}

	@Test
	public void testAcceptRequest() {
		acceptFriendRequest(10, 18);
	}

	@Test
	public void testGetPlaces() {
		System.out.println(getPlaces().size());
	}


}
